package org.csu.onlineedubackend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.csu.onlineedubackend.dto.StudentCountDTO;
import org.csu.onlineedubackend.entity.StudentCourse;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface StudentCourseMapper extends BaseMapper<StudentCourse> {
    /**
     * 获取每月活跃学生数（月活）
     * @return 每月活跃学生数列表，包含月份和人数
     */
    @Select("SELECT DATE_FORMAT(last_learn_time, '%Y-%m') AS month, COUNT(DISTINCT student_id) AS activeCount FROM student_course WHERE last_learn_time IS NOT NULL GROUP BY month ORDER BY month")
    List<Map<String, Object>> getMonthlyActiveStudents();


    // 通过id获取学生人数
    @Select({
            "<script>",
            "SELECT COUNT(DISTINCT student_id) AS studentCount",
            "FROM student_course",
            "WHERE status = 'paid'",
            "AND course_id IN",
            "<foreach collection='courseIds' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    Integer getStudentCountByCourseIds(@Param("courseIds") List<Integer> courseIds);
    
    // 获得学生完成课程数
    @Select("SELECT COUNT(*) FROM student_course WHERE student_id = #{studentId} AND status = 'paid' AND progress >= 100")
    int countFinishedCourses(@Param("studentId") Integer studentId);

    // 2. 批量统计所有学生完成课程数
    @Select("""
    SELECT student_id, COUNT(*) AS count
    FROM student_course
    WHERE status = 'paid' AND progress >= 100
    GROUP BY student_id
""")
    List<StudentCountDTO> countFinishedCoursesByStudentIds();

    /**
     * 獲取學生訂閱的所有課程
     * @param userId 學生ID
     * @return 課程列表
     */
    @Select("SELECT c.id, c.title as name, c.cover_url as image FROM course c " +
            "JOIN course_subscribe cs ON c.id = cs.course_id " +
            "WHERE cs.student_id = #{userId}")
    List<Map<String, Object>> getSubscribedCourses(Long userId);
}
